using System;
using Gtk;
using Mono.Data.Sqlite;
using System.Linq;
using Mono.Unix;
using nwind;
using System.Collections;
using System.Text;

public partial class MainWindow: Gtk.Window
{	
	nwind.Main db;
	private static Random random = new Random((int)DateTime.Now.Ticks);
	public Gtk.ListStore student_store;
	public Gtk.TreeModelFilter student_filter;
	public bool loaded; /* stop second read from db */
	public MainWindow (): base (Gtk.WindowType.Toplevel)
	{
		Build ();

		var conn = new SqliteConnection(
			"DbLinqProvider=Sqlite;" +
			"Data Source = db_qlhs.db");
		db = new nwind.Main(conn);

		student_store = new Gtk.ListStore(typeof(Student));
		loaded = false;

		Catalog.Init ("i18n", "./locale");
		btn_deletecur.Label = Catalog.GetString("Delete current record");
		btn_find.Label = Catalog.GetString ("Clear Fields");
		btn_loaddb.Label = Catalog.GetString ("Load records");
		btn_savedb.Label = Catalog.GetString ("Save records");
		btn_updatecur.Label = Catalog.GetString ("Update current records");
		btn_add_student.Label = Catalog.GetString ("Add student");

		btn_loaddb.Clicked += OnLoadDb;
		btn_updatecur.Clicked += OnUpdateStudent;
		btn_savedb.Clicked += OnSaveDb;
		btn_deletecur.Clicked += OnDeleteStudent;
		btn_find.Clicked += OnClearFields;
		btn_add_student.Clicked += OnAddStudent;

		lb_studentchem.UseMarkup = true;
		lb_studentid.UseMarkup = true;
		lb_studentlit.UseMarkup = true;
		lb_studentmath.UseMarkup = true;
		lb_studentname.UseMarkup = true;
		lb_studentphone.UseMarkup = true;
		lb_studentphy.UseMarkup = true;

		lb_studentchem.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Chemistry") + "</span>";
		lb_studentid.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Student ID") + "</span>";
		lb_studentlit.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Literature") + "</span>";
		lb_studentmath.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Math") + "</span>";
		lb_studentname.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Name") + "</span>";
		lb_studentphone.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Phone") + "</span>";
		lb_studentphy.LabelProp = "<span weight=\"bold\">" + Catalog.GetString ("Physics") + "</span>";

		e_studentchem.Changed += OnFieldChanged;
		e_studentid.Changed += OnFieldChanged;
		e_studentlit.Changed += OnFieldChanged;
		e_studentmath.Changed += OnFieldChanged;
		e_studentname.Changed += OnFieldChanged;
		e_studentphone.Changed += OnFieldChanged;
		e_studentphy.Changed += OnFieldChanged;


		Gtk.TreeViewColumn student_id_col = new Gtk.TreeViewColumn();
		student_id_col.Title = Catalog.GetString ("Stu ID");
		Gtk.CellRendererText student_id_cell = new Gtk.CellRendererText();
		student_id_col.PackStart(student_id_cell, true);
		student_id_col.SetCellDataFunc(student_id_cell, new Gtk.TreeCellDataFunc(RenderStudentID));

		Gtk.TreeViewColumn student_name_col = new Gtk.TreeViewColumn();
		student_name_col.Title = Catalog.GetString ("Name");
		Gtk.CellRendererText student_name_cell = new Gtk.CellRendererText();
		student_name_col.PackStart(student_name_cell, true);
		student_name_col.SetCellDataFunc(student_name_cell, new Gtk.TreeCellDataFunc(RenderStudentName));

		Gtk.TreeViewColumn student_phone_col = new Gtk.TreeViewColumn();
		student_phone_col.Title = Catalog.GetString ("Phone");
		Gtk.CellRendererText student_phone_cell = new Gtk.CellRendererText();
		student_phone_col.PackStart(student_phone_cell, true);
		student_phone_col.SetCellDataFunc(student_phone_cell, new Gtk.TreeCellDataFunc(RenderStudentPhone));

		student_filter = new Gtk.TreeModelFilter(student_store, null);
		student_filter.VisibleFunc = new Gtk.TreeModelFilterVisibleFunc (FilterStudent);

		trv_student.AppendColumn(student_id_col);
		trv_student.AppendColumn(student_name_col);
		trv_student.AppendColumn(student_phone_col);
		trv_student.Model = student_filter;
		trv_student.CursorChanged += OnStudentChanged;

	}

	private void RenderStudentID (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
	{
		Student stu = (Student)model.GetValue (iter, 0);
		(cell as Gtk.CellRendererText).Text = stu.ID;
	}

	private void RenderStudentName (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
	{
		Student stu = (Student)model.GetValue (iter, 0);
		(cell as Gtk.CellRendererText).Text = stu.StudentName;
	}

	private void RenderStudentPhone (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
	{
		Student stu = (Student)model.GetValue (iter, 0);
		(cell as Gtk.CellRendererText).Text = stu.Phone;
	}
	private void OnFieldChanged (object o, System.EventArgs args)
	{
		student_filter.Refilter ();
	}

	private bool FilterStudent (Gtk.TreeModel model, Gtk.TreeIter iter)
	{
		Student stu = (Student)model.GetValue (iter, 0);

		bool return_val_chem;
		bool return_val_stuid;
		bool return_val_lit;
		bool return_val_math;
		bool return_val_stuname;
		bool return_val_stuphone;
		bool return_val_stuphy;

		/* FIXME : strict match */


		if (e_studentchem.Text != "") {
			if (stu.GradeGrade.Chemistry == e_studentchem.Text)
				return_val_chem = true;
			else 
				return_val_chem = false;
		} else
			return_val_chem = true;

		if (e_studentid.Text != "") {
			if (stu.ID == e_studentid.Text)
				return_val_stuid = true;
			else
				return_val_stuid = false;
		} else
			return_val_stuid = true;

		if (e_studentlit.Text != "") {
			if (stu.GradeGrade.Literature == e_studentlit.Text)
				return_val_lit = true;
			else
				return_val_lit = false;
		} else
			return_val_lit = true;

		if (e_studentmath.Text != "") {
			if (stu.GradeGrade.Maths == e_studentmath.Text)
				return_val_math = true;
			else
				return_val_math = false;
		} else 
			return_val_math = true;

		if (e_studentname.Text != "") {
			if (stu.StudentName == e_studentname.Text)
				return_val_stuname = true;
			else 
				return_val_stuname = false;
		} else 
			return_val_stuname = true;


		if (e_studentphone.Text != "") {
			if (stu.Phone == e_studentphone.Text)
				return_val_stuphone = true;
			else
				return_val_stuphone = false;
		} else
			return_val_stuphone = true;

		if (e_studentphy.Text != "") {
			if (stu.GradeGrade.Physics == e_studentphy.Text)
				return_val_stuphy = true;
			else
				return_val_stuphy = false;
		} else
			return_val_stuphy = true;

		return return_val_chem && return_val_stuid
			&& return_val_lit && return_val_lit && return_val_math
				&& return_val_stuname && return_val_stuphone & return_val_stuphy;
	}
	/* nhap ds tu man hinh, hien thi danh sach, cap nhat mot hoc sinh, xoa mot hoc sinh, tim hoc sinh */
	protected void OnDeleteEvent (object sender, DeleteEventArgs a)
	{
		Application.Quit ();
		a.RetVal = true;
	}
	protected void OnLoadDb (object sender, EventArgs e)
	{
		if (loaded == true)
			return;
		var students = from s in db.Student
			select s;
		foreach (Student stu in students) {
			student_store.AppendValues(stu);
		}
		loaded = true;
	}
	protected void OnStudentChanged (object obj, EventArgs args)
	{
		Gtk.TreeModel model = trv_student.Model;
		Gtk.TreeIter iter;
		trv_student.Selection.GetSelected (out model, out iter);

		Student stu = (Student)model.GetValue (iter, 0);

		Grade gra = stu.GradeGrade;

		e_studentchem.Text = gra.Chemistry;
		e_studentid.Text = stu.ID;
		e_studentlit.Text = gra.Literature;
		e_studentmath.Text = gra.Maths;
		e_studentname.Text = stu.StudentName;
		e_studentphone.Text = stu.Phone;
		e_studentphy.Text = gra.Physics;

	}

	protected void OnUpdateStudent (object obj, EventArgs args)
	{
		var that_student = from s in db.Student
			where s.ID == e_studentid.Text
				select s;
		foreach (Student stu in that_student)
		{
			/* FIXME : no allowance to change student id */
			stu.StudentName = e_studentname.Text;
			stu.GradeGrade.Chemistry = e_studentchem.Text;
			stu.GradeGrade.Literature = e_studentlit.Text;
			stu.GradeGrade.Maths = e_studentmath.Text;
			stu.Phone = e_studentphone.Text;
			stu.GradeGrade.Physics = e_studentphy.Text;
		}
		db.SubmitChanges();
	}

	protected void OnDeleteStudent (object obj, EventArgs args)
	{
		var that_student = from s in db.Student
			where s.ID == e_studentid.Text
				select s;

		Gtk.TreeModel model = trv_student.Model;
		Gtk.TreeIter iter;

		trv_student.Selection.GetSelected(out model, out iter);

		student_store.Remove (ref iter);

		foreach (Student stu in that_student)
		{
			var his_grade = from g in db.Grade
				where g.StudentRef == stu.Grade
					select g;
			db.Grade.DeleteAllOnSubmit(his_grade);
		}

		Console.WriteLine ("Reach Delete all");
		db.Student.DeleteAllOnSubmit(that_student);
		db.SubmitChanges();

		OnClearFields(obj, args);
		student_store.Clear ();
		loaded = false;
		OnLoadDb(obj, args);
	}

	protected void OnSaveDb (object obj, EventArgs args)
	{
		int col = 0;
		ArrayList grade_array = new ArrayList();
		ArrayList student_array = new ArrayList();
		Student stu;
		Grade grade;
		foreach (object[] row in student_store) {
			stu = (Student)row [col];
			/* Manually save Grade object */
			grade = stu.GradeGrade;
			grade_array.Add (grade);
			student_array.Add (stu);
		}

		/* wipe out the database */
		var students = from s in db.Student
			select s;
		db.Student.DeleteAllOnSubmit (students);

		var grades = from g in db.Grade
			select g;
		db.Grade.DeleteAllOnSubmit (grades);

		db.SubmitChanges ();

		foreach (Student s in student_array) {
			db.Student.InsertOnSubmit(s);
		}

		foreach (Grade g in grade_array) {
			db.Grade.InsertOnSubmit(g);
		}

		db.SubmitChanges();
	}

	protected void OnClearFields (object obj, EventArgs args)
	{
		e_studentchem.Text = "";
		e_studentid.Text = "";
		e_studentlit.Text = "";
		e_studentmath.Text = "";
		e_studentname.Text = "";
		e_studentphone.Text = "";
		e_studentphy.Text = "";
	}

	protected void OnAddStudent (object obj, EventArgs args)
	{
		Student stu = new Student ();
		Grade gr = new Grade ();
		gr.Chemistry = e_studentchem.Text;
		stu.ID = e_studentid.Text;
		gr.Literature = e_studentlit.Text;
		gr.Maths = e_studentmath.Text;
		stu.StudentName = e_studentname.Text;
		stu.Phone = e_studentphone.Text;
		gr.Physics = e_studentphy.Text;

		/* FIXME: link between grade and student must be create manually,
		 * why not link betweent student grade using grade (int) in student
		 * to grade_id(int) PRI in grade ? Compiler throw exception tell it is not supported,
		 * how about random string */

		string bridge = RandomString();
		stu.Grade = bridge;
		gr.StudentRef = bridge;

		db.Student.InsertOnSubmit(stu);
		db.Grade.InsertOnSubmit(gr);

		db.SubmitChanges();

		OnClearFields(obj, args);
		student_store.Clear ();
		loaded = false;
		OnLoadDb(obj, args);

	}

	public string RandomString ()
	{
		StringBuilder builder = new StringBuilder();
		char ch;
		for (int i = 0; i < 5; i++)
		{
			ch = Convert.ToChar(Convert.ToInt32 (Math.Floor (26 * random.NextDouble() + 65)));
			builder.Append (ch);
		}
		return builder.ToString();
	}
			                                                
}